import mysql from 'mysql2';

const db_config = {
  host: 'localhost',
  user: 'root',
  password: 'root',
  port: 3306,
  database: 'user'
};
// 创建数据库连接池
const pool = mysql.createPool(db_config);

/**
 * useMysql 方法
 * @param {string} sql - SQL 查询语句
 * @param {Array} params - 查询参数（可选）
 * @returns {Promise<any>} 查询结果
 */
export async function useMysql(sql, params = []) {
  return new Promise((resolve, reject) => {
    // 检查是否传入参数
    if (params && params.length > 0) {
      // 如果有参数，执行参数化查询
      pool.query(sql, params, (err, result) => {
        if (err) {
          console.error(`查询失败: ${err}`);
          reject(err);
          return;
        }
        resolve(result);
      });
    } else {
      // 如果没有参数，直接执行 SQL 语句
      pool.query(sql, (err, result) => {
        if (err) {
          console.error(`查询失败: ${err}`);
          reject(err);
          return;
        }
        resolve(result);
      });
    }
  });
}

/**
 * MySQL查询语句生成器（返回完整SQL语句版本）
 */

export const MysqlQueryBuilder = {
    /**
     * 基础查询
     * @param {string} table 表名
     * @param {string[]} fields 查询字段数组
     * @param {object} conditions 条件对象
     * @param {object} options 选项 {limit, offset, orderBy, groupBy}
     * @returns {string} 完整SQL查询语句
     */
    select: (table, fields = ['*'], conditions = {}, options = {}) => {
      const fieldList = fields.join(', ');
      let sql = `SELECT ${fieldList} FROM ${table}`;
      
      // 添加WHERE条件
      if (Object.keys(conditions).length > 0) {
        const whereClause = Object.entries(conditions)
          .map(([key, value]) => {
            // 处理特殊值
            if (value === null) return `${key} IS NULL`;
            if (Array.isArray(value)) return `${key} IN (${value.map(v => formatValue(v)).join(', ')})`;
            return `${key} = ${formatValue(value)}`;
          })
          .join(' AND ');
        sql += ` WHERE ${whereClause}`;
      }
      
      // 添加其他选项
      if (options.groupBy) sql += ` GROUP BY ${options.groupBy}`;
      if (options.orderBy) sql += ` ORDER BY ${options.orderBy}`;
      if (options.limit) sql += ` LIMIT ${options.limit}`;
      if (options.offset) sql += ` OFFSET ${options.offset}`;
      
      return sql;
    },
    
    /**
     * 插入数据
     * @param {string} table 表名
     * @param {object|object[]} data 要插入的数据对象或数组
     * @param {object} options 选项 {ignore, updateOnDuplicate}
     * @returns {string} 完整SQL插入语句
     */
    insert: (table, data, options = {}) => {
      const isBulk = Array.isArray(data);
      const records = isBulk ? data : [data];
      
      if (records.length === 0) throw new Error('插入数据不能为空');
      
      // 获取列名
      const columns = Object.keys(records[0]);
      
      // 构建VALUES部分
      const values = records.map(record => 
        `(${columns.map(col => formatValue(record[col])).join(', ')})`
      ).join(', ');
      
      // 构建基础SQL
      let sql = `INSERT ${options.ignore ? 'IGNORE ' : ''}INTO ${table} `;
      sql += `(${columns.join(', ')}) VALUES ${values}`;
      
      // 处理重复键更新(有更无增)
      if (options.updateOnDuplicate) {
        if (Array.isArray(options.updateOnDuplicate)) {
          sql += ` ON DUPLICATE KEY UPDATE ${
            options.updateOnDuplicate.map(col => `${col}=VALUES(${col})`).join(', ')
          }`;
        } else if (typeof options.updateOnDuplicate === 'object') {
          sql += ` ON DUPLICATE KEY UPDATE ${
            Object.entries(options.updateOnDuplicate)
              .map(([key, val]) => `${key}=${formatValue(val)}`)
              .join(', ')
          }`;
        }
      }
      
      return sql;
    },
    
    /**
     * 更新数据
     * @param {string} table 表名
     * @param {object} data 要更新的数据对象
     * @param {object} conditions 条件对象
     * @param {object} options 选项 {limit, orderBy}
     * @returns {string} 完整SQL更新语句
     */
    update: (table, data, conditions = {}, options = {}) => {
      const setClause = Object.entries(data)
        .map(([key, value]) => `${key} = ${formatValue(value)}`)
        .join(', ');
      
      let sql = `UPDATE ${table} SET ${setClause}`;
      
      // 添加WHERE条件
      if (Object.keys(conditions).length > 0) {
        const whereClause = Object.entries(conditions)
          .map(([key, value]) => {
            if (value === null) return `${key} IS NULL`;
            if (Array.isArray(value)) return `${key} IN (${value.map(v => formatValue(v)).join(', ')})`;
            return `${key} = ${formatValue(value)}`;
          })
          .join(' AND ');
        sql += ` WHERE ${whereClause}`;
      }
      
      // 添加其他选项
      if (options.orderBy) sql += ` ORDER BY ${options.orderBy}`;
      if (options.limit) sql += ` LIMIT ${options.limit}`;
      
      return sql;
    },
    
    /**
     * 删除数据
     * @param {string} table 表名
     * @param {object} conditions 条件对象
     * @param {object} options 选项 {limit, orderBy}
     * @returns {string} 完整SQL删除语句
     */
    delete: (table, conditions = {}, options = {}) => {
      let sql = `DELETE FROM ${table}`;
      
      // 添加WHERE条件
      if (Object.keys(conditions).length > 0) {
        const whereClause = Object.entries(conditions)
          .map(([key, value]) => {
            if (value === null) return `${key} IS NULL`;
            if (Array.isArray(value)) return `${key} IN (${value.map(v => formatValue(v)).join(', ')})`;
            return `${key} = ${formatValue(value)}`;
          })
          .join(' AND ');
        sql += ` WHERE ${whereClause}`;
      }
      
      // 添加其他选项
      if (options.orderBy) sql += ` ORDER BY ${options.orderBy}`;
      if (options.limit) sql += ` LIMIT ${options.limit}`;
      
      return sql;
    }
  };
  
  // 辅助函数：格式化值
  function formatValue(value) {
    if (value === null) return 'NULL';
    if (value === undefined) return 'NULL';
    if (typeof value === 'string') return `'${escapeString(value)}'`;
    if (value instanceof Date) return `'${value.toISOString().slice(0, 19).replace('T', ' ')}'`;
    if (typeof value === 'boolean') return value ? '1' : '0';
    return value;
  }
  
  // 辅助函数：转义字符串
  function escapeString(str) {
    return str.replace(/[\0\n\r\b\t\\'"\x1a]/g, function(char) {
      switch (char) {
        case '\0': return '\\0';
        case '\n': return '\\n';
        case '\r': return '\\r';
        case '\b': return '\\b';
        case '\t': return '\\t';
        case '\x1a': return '\\Z';
        case '\'': return '\\\'';
        case '"': return '\\"';
        case '\\': return '\\\\';
        default: return char;
      }
    });
  }